package org.jeecg;

import org.geotools.data.DataStore;
import org.geotools.data.DataStoreFinder;
import org.geotools.data.simple.SimpleFeatureCollection;
import org.geotools.data.simple.SimpleFeatureIterator;
import org.geotools.geometry.jts.JTS;
import org.geotools.referencing.CRS;
import org.locationtech.jts.geom.*;
import org.locationtech.jts.io.WKBWriter;
import org.opengis.feature.simple.SimpleFeature;
import org.opengis.referencing.FactoryException;
import org.opengis.referencing.crs.CoordinateReferenceSystem;
import org.opengis.referencing.operation.MathTransform;

import java.io.File;
import java.sql.*;
import java.util.Map;

public class Shp2PostGIS_WGS84_Resumable {

    // 坐标系转换器（32650 -> 4326）
    private static MathTransform transform;

    static {
        try {
            CoordinateReferenceSystem sourceCRS = CRS.decode("EPSG:32650");
            CoordinateReferenceSystem targetCRS = CRS.decode("EPSG:4326");
            transform = CRS.findMathTransform(sourceCRS, targetCRS, true);
        } catch (FactoryException e) {
            throw new RuntimeException("坐标系转换器初始化失败", e);
        }
    }

    // 配置参数
    private static final String JDBC_URL = "jdbc:postgresql://123.56.202.52:5432/gisdb";
    private static final String USERNAME = "postgres";
    private static final String PASSWORD = "wwq20010215";
    private static final String TABLE_NAME = "geotype_wgs84";

    // 批量大小
    private static final int BATCH_SIZE = 5000;

    // 状态文件存储位置

    public static void main(String[] args) {
        String shpPath = "F:\\地质数据\\地形坡度\\地形坡度底图.shp";

        Connection conn = null;
        PreparedStatement pstmt = null;
        SimpleFeatureIterator iterator = null;
        DataStore shpDataStore = null;
        WKBWriter wkbWriter = new WKBWriter();
        long programStart = System.currentTimeMillis();

        try {
            // 1. 初始化数据库连接
            conn = DriverManager.getConnection(JDBC_URL, USERNAME, PASSWORD);
            conn.setAutoCommit(false);

            // 2. 查询数据库现有记录数
            long existingCount = getRecordCount(conn, TABLE_NAME);
            System.out.println("数据库现有记录数: " + existingCount);

            // 3. 加载Shapefile
            System.out.println("正在打开Shapefile: " + shpPath);
            File shpFile = new File(shpPath);
            shpDataStore = DataStoreFinder.getDataStore(Map.of(
                    "url", shpFile.toURI().toURL(),
                    "memory mapped buffer", Boolean.TRUE
            ));
            if (shpDataStore == null) throw new RuntimeException("无法加载Shapefile数据源");

            String typeName = shpDataStore.getTypeNames()[0];
            SimpleFeatureCollection features = shpDataStore.getFeatureSource(typeName).getFeatures();
            long featureCount = features.size();
            System.out.println("Shapefile总记录数: " + featureCount);

            // 4. 创建目标表（如果不存在）
            createTableIfNotExists(conn, TABLE_NAME);

            // 5. 准备插入语句（只包含三个字段）
            String insertSQL = "INSERT INTO " + TABLE_NAME + " (id, the_geom, gridcode) " +
                    "VALUES (?, ST_GeomFromWKB(?, 4326), ?)";
            pstmt = conn.prepareStatement(insertSQL);

            // 6. 获取Feature迭代器
            iterator = features.features();

            // 7. 导入数据（从现有记录数之后开始）
            int currentBatch = 0;
            long processedThisRun = 0;
            long batchStartTime = System.currentTimeMillis();
            long lastProgressTime = System.currentTimeMillis();
            long startId = existingCount + 1; // 从现有记录数+1开始

            System.out.printf("从ID %d 开始导入...%n", startId);

            while (iterator.hasNext()) {
                SimpleFeature feature = iterator.next();
                Long currentId = (Long) feature.getAttribute("Id");

                // 跳过小于起始ID的记录
                if (currentId == null || currentId < startId) {
                    continue;
                }

                try {
                    // 几何对象 - 转换为WGS84坐标系
                    MultiPolygon originalGeom = (MultiPolygon) feature.getAttribute("the_geom");
                    Geometry transformedGeom = JTS.transform(originalGeom, transform);

                    // gridcode (Long)
                    Long gridcode = (Long) feature.getAttribute("gridcode");

                    // 设置参数
                    pstmt.setLong(1, currentId);
                    pstmt.setBytes(2, wkbWriter.write(transformedGeom));
                    pstmt.setLong(3, gridcode != null ? gridcode : -1);

                    pstmt.addBatch();
                    currentBatch++;
                    processedThisRun++;

                    // 定期提交批次
                    if (currentBatch >= BATCH_SIZE) {
                        executeBatch(conn, pstmt, currentBatch);
                        currentBatch = 0;
                        batchStartTime = System.currentTimeMillis();

                        // 保存进度状态
                        System.out.printf("已导入: %d 条, 最后ID: %d%n",
                                processedThisRun, currentId);
                    }

                    // 每5秒打印一次进度
                    long currentTime = System.currentTimeMillis();
                    if (currentTime - lastProgressTime > 5000) {
                        lastProgressTime = currentTime;
                        System.out.printf("进度: %d/%d 条 (%.1f%%)%n",
                                currentId, featureCount,
                                (currentId * 100.0) / featureCount);
                    }
                } catch (Exception e) {
                    System.err.println("处理记录失败: " + e.getMessage());
                    break;
                }
            }

            // 执行最后一批
            if (currentBatch > 0) {
                executeBatch(conn, pstmt, currentBatch);
            }

            // 8. 完成导入
            long lastId = getMaxId(conn, TABLE_NAME);
            if (lastId >= featureCount) {
                System.out.println("✅ 所有记录导入完成!");

                // 创建空间索引
                createSpatialIndex(conn, TABLE_NAME);
                System.out.println("空间索引创建完成");

                // 验证坐标系
                verifyCoordinateSystem(conn, TABLE_NAME);
            } else {
                System.out.printf("⏸️ 导入暂停! 下次将从ID %d 继续%n", lastId + 1);
            }

            // 9. 验证记录数
            long dbCount = getRecordCount(conn, TABLE_NAME);
            System.out.println("========================================");
            System.out.printf("数据库现有记录数: %d 条%n", dbCount);
            System.out.printf("Shapefile总记录数: %d 条%n", featureCount);
            System.out.printf("本次导入记录数: %d 条%n", processedThisRun);
            System.out.printf("累计已导入: %d 条%n", dbCount);
            System.out.println("========================================");

        } catch (Exception e) {
            System.err.println("导入失败: " + e.getMessage());
            e.printStackTrace();
        } finally {
            closeResources(iterator, shpDataStore, pstmt, conn);

            long totalProgramTime = System.currentTimeMillis() - programStart;
            System.out.println("========================================");
            System.out.printf("程序执行时间: %d ms (%.1f 分钟)%n",
                    totalProgramTime, totalProgramTime / 60000.0);
            System.out.println("========================================");
        }
    }

    // ================ 数据库操作 ================ //

    /**
     * 创建表（如果不存在）
     */
    private static void createTableIfNotExists(Connection conn, String tableName)
            throws SQLException {
        try (Statement stmt = conn.createStatement()) {
            // 检查表是否存在
            if (!tableExists(conn, tableName)) {
                System.out.println("创建表: " + tableName);
                String createSQL = "CREATE TABLE " + tableName + " (" +
                        "id BIGINT PRIMARY KEY, " +
                        "the_geom GEOMETRY(MultiPolygon, 4326), " +
                        "gridcode BIGINT)";
                stmt.execute(createSQL);
                conn.commit();
                System.out.println("表创建成功");
            } else {
                System.out.println("表已存在: " + tableName);
            }
        }
    }

    /**
     * 检查表是否存在
     */
    private static boolean tableExists(Connection conn, String tableName)
            throws SQLException {
        try (ResultSet rs = conn.getMetaData().getTables(null, null, tableName, null)) {
            return rs.next();
        }
    }

    /**
     * 执行批处理（高效优化版）
     */
    private static void executeBatch(Connection conn, PreparedStatement pstmt, int batchSize)
            throws SQLException {
        long start = System.currentTimeMillis();
        try {
            int[] results = pstmt.executeBatch();
            conn.commit();
            long duration = System.currentTimeMillis() - start;

            // 计算成功插入的数量
            int successCount = 0;
            for (int result : results) {
                if (result >= 0) successCount++;
            }

            double rate = successCount * 1000.0 / duration;
            System.out.printf("✅ 提交批次: %d条, 耗时: %d ms, 速率: %.0f 条/秒%n",
                    successCount, duration, rate);
        } catch (BatchUpdateException e) {
            System.err.println("批处理失败: " + e.getMessage());
            conn.rollback();
            throw e;
        } finally {
            pstmt.clearBatch();
        }
    }

    /**
     * 获取数据库记录数
     */
    private static long getRecordCount(Connection conn, String tableName) throws SQLException {
        String sql = "SELECT COUNT(*) FROM " + tableName;
        try (Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            if (rs.next()) {
                return rs.getLong(1);
            }
        }
        return 0;
    }

    /**
     * 获取最大ID
     */
    private static long getMaxId(Connection conn, String tableName) throws SQLException {
        String sql = "SELECT MAX(id) FROM " + tableName;
        try (Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {
            if (rs.next()) {
                return rs.getLong(1);
            }
        }
        return 0;
    }

    // ================ 空间索引和验证 ================ //

    /**
     * 创建空间索引
     */
    private static void createSpatialIndex(Connection conn, String tableName)
            throws SQLException {
        try (Statement stmt = conn.createStatement()) {
            String indexSQL = "CREATE INDEX " + tableName + "_geom_idx " +
                    "ON " + tableName + " USING GIST (the_geom)";
            stmt.execute(indexSQL);
            conn.commit();
            System.out.println("空间索引创建成功");

            // 收集统计信息
            stmt.execute("ANALYZE " + tableName);
            conn.commit();
            System.out.println("统计信息更新完成");
        }
    }

    /**
     * 验证坐标系
     */
    private static void verifyCoordinateSystem(Connection conn, String tableName)
            throws SQLException {
        try (Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(
                     "SELECT ST_SRID(the_geom) AS srid FROM " + tableName + " LIMIT 1")) {

            if (rs.next()) {
                int srid = rs.getInt("srid");
                if (srid == 4326) {
                    System.out.println("✅ 坐标系验证成功: SRID=" + srid);
                } else {
                    System.out.println("❌ 坐标系验证失败: SRID=" + srid + " (期望4326)");
                }
            }
        }
    }

    // ================ 资源清理 ================ //

    /**
     * 安全关闭所有资源
     */
    private static void closeResources(SimpleFeatureIterator iterator, DataStore shpDataStore,
                                       PreparedStatement pstmt, Connection conn) {
        try {
            if (iterator != null) iterator.close();
        } catch (Exception e) {
            System.err.println("关闭iterator失败: " + e.getMessage());
        }
        try {
            if (shpDataStore != null) shpDataStore.dispose();
        } catch (Exception e) {
            System.err.println("关闭shpDataStore失败: " + e.getMessage());
        }
        try {
            if (pstmt != null) pstmt.close();
        } catch (SQLException e) {
            System.err.println("关闭pstmt失败: " + e.getMessage());
        }
        try {
            if (conn != null && !conn.isClosed()) {
                conn.close();
            }
        } catch (SQLException e) {
            System.err.println("关闭conn失败: " + e.getMessage());
        }
    }
}